using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Tab2Sql.Classes.db;
using Tab2Sql.GarbageIn;

namespace Tab2Sql.Forms
{
    /// <summary>
    ///
    /// </summary>
    public class GarbageIn
    {
        private StringBuilder output;
        /// <summary>
        ///
        /// </summary>
        public StringBuilder Output
        {
            get { return output; }
            set { output = value; }
        }
        private List<string> matchList;
        private SqliteDB sDB;
        private DataTable AddressList;
        /// <summary>
        ///
        /// </summary>
        public List<string> MatchList
        {
            get { return matchList; }
        }
        /// <summary>
        ///
        /// </summary>
        public GarbageIn()
        {
            output = new StringBuilder();
            matchList = new List<string>();
            sDB = new SqliteDB(@"H:\me\PWK\tab2sql\tab2sql\bin\Release\the.db");
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="input">
        /// A <see cref="System.String[]"/>
        /// </param>
        public void MatchUPRN(string[] input)
        {
            List<Address> uprnList = ConvertToAddressList(input,FieldType.UPRN);

            List<Address> addressList = GetAddressList();
            var Addy = from p1 in uprnList
                       join o1 in addressList on p1.UPRN equals o1.UPRN
                       into addListX 
                       from add in addListX.DefaultIfEmpty(new Address())
                       orderby p1.ID
                       select new { p1.ID,p1.UPRN,add.Add_1 }   ;
            foreach (var i in Addy)
            {
                output.AppendFormat("{0} \t {1} \t   {2}\n",i.ID, i.UPRN, i.Add_1);
            }
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="input">
        /// A <see cref="System.String[]"/>
        /// </param>
        public void MatchAddress(string[] input)
        {
            List<Address> uprnList = ConvertToAddressList(input, FieldType.Add_1);

            List<Address> addressList = GetAddressList();
            var Addy = from p1 in uprnList
                       join o1 in addressList on p1.Add_1 equals o1.Add_1
                       into addListX
                       from add in addListX.DefaultIfEmpty(new Address())
                       orderby p1.ID
                       select new { p1.ID, add.UPRN, p1.Add_1 };
            foreach (var i in Addy)
            {
                output.AppendFormat("{0} \t {1} \t   {2}\n", i.ID,  i.Add_1,i.UPRN);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="input">
        /// A <see cref="System.String[]"/>
        /// </param>
        public void MatchImperfect(string[] input)
        {
            List<Address> uprnList = ConvertToAddressList(input, FieldType.Unknown);

            List<Address> addressList = GetAddressList(true);
            var Addy = from p1 in uprnList
                       join o1 in addressList on
                       new {p1.Add_1_Six,p1.Stub} equals new {o1.Add_1_Six,o1.Stub}
                       
                       orderby p1.ID
                       select new { p1.ID, p1.Add_1_Six,p1.Post_Stub, p1.Door, p1.District,o1.Add_1,o1.UPRN ,o1.Full_Address,p1.Full_Address2,p1.Stub};

            Addy = from px in Addy
                   join o1 in addressList on
                   px.Stub equals o1.Stub
                   orderby px.ID
                   select new { px.ID, px.Add_1_Six, px.Post_Stub, px.Door, px.District, o1.Add_1, o1.UPRN, o1.Full_Address, px.Full_Address2, px.Stub };
            
             
            
            
            
            //Addy = from p0 in
            //           Addy.Union(from p1 in uprnList
            //                      join o1 in addressList on
            //                      new { p1.Add_1_Six ,p1.District}
            //                      equals
            //                      new { o1.Add_1_Six, o1.District }
            //                      into addListX
            //                      from add in addListX.DefaultIfEmpty(new Address())
            //                      orderby p1.ID
            //                      select new { p1.ID, p1.Add_1_Six, p1.Post_Stub, p1.Door, p1.District,add.Add_1, add.UPRN,add.Full_Address,p1.Full_Address2 })
            //       select p0;


            var Addy2 = from n1 in Addy.Distinct()
                        where n1.UPRN !=null
                        group n1 by n1.Full_Address2 into g
                        select new { Term = g.Key, resultCount = g.Count() ,resultFirst = g.First(), resultLast=g.Last()};
                        
        
            foreach (var moo in Addy2)
            {

                string a = moo.Term.ToString();
           
                int mCount = moo.resultCount;
                string b = moo.resultFirst.Full_Address;
                string c = moo.resultLast.Full_Address;
                if (mCount > 1 && b != c)
                {
                    output.AppendFormat("\n--------------------->\t{0}\t|{1}\t|{2}\t|{3}\t|\t", a, b, c, mCount);
                }
                else 
                {
                    output.AppendFormat("\n->\t{0}\t|{1}\t|{2}\t|{3}\t|\t", a, b, c, mCount);
                }
            }

            //int finalCounter = 0;
            //foreach (var i in Addy)
            //{
            //    if (i.UPRN !=null)
            //    {

            //        long Score = HowSimilar.howSimilarX(i.Full_Address, i.Full_Address2);
            //       // output.AppendFormat("{0}\t|{1}\t|{2}\t|{3}\t|{4}\t|{5}|\t{6}|\t{7}",  i.ID, i.Add_1_Six, i.Post_Stub, i.Door, i.District,i.Add_1, i.UPRN);
            //       // output.AppendFormat("\t{0}\t|{1}\t|{2}\t|{3}\t\n", finalCounter,Score, i.Full_Address, i.Full_Address2);

            //        finalCounter++;
            //    }
            //}
        }

      




        enum FieldType
        {
            UPRN,
            Add_1,
            Unknown
        }

        private static List<Address> ConvertToAddressList(string[] input,FieldType fieldType)
        {
            int ix2 = 0;
            List<Address> uprnList = new List<Address>();
            foreach (string s in input)
            {
                ix2++;
                if (s.Length > 0)
                {
                    switch (fieldType)
                    {
                        case FieldType.UPRN:
                            uprnList.Add(new Address { ID = ix2, UPRN = s, Add_1 = "UNKNOWN" });
                            break;
                        case FieldType.Add_1:
                            uprnList.Add(new Address { ID = ix2, UPRN = "UNKNOWN", Add_1 = s });
                            break;
                        case FieldType.Unknown:
                            uprnList.Add(new Address(s,ix2));
                            break;
                        default:
                            uprnList.Add(new Address { ID = ix2, UPRN = "UNKNOWN", Add_1 = s });
                            break;
                    }
                    
                }
            }
            return uprnList;
        }




        private List<Address> GetAddressList()
        {
            AddressList = sDB.RenderSql("SELECT Lo_Code,UPRN, Add_1 FROM Lite");
            List<Address> addressList = new List<Address>();
            foreach (DataRow dr in AddressList.Rows)
            {

                addressList.Add(new Address { ID = Convert.ToInt32(dr["Lo_Code"]), UPRN = dr["UPRN"].ToString(), Add_1 = dr["Add_1"].ToString() });

            }
            return addressList;
        }
        private List<Address> GetAddressList(bool variant)
        {
            int iCounter = 0;
            AddressList = sDB.RenderSql("SELECT Lo_Code,UPRN,Add_1,Add_2,Add_3,District,House_No, House_NO_Suffix,Post_Code,Post_Stub,Full_Address FROM staticAddress where tenure<>'DIS'");
            List<Address> addressList = new List<Address>();
            foreach (DataRow dr in AddressList.Rows)
            {
                addressList.Add(new Address(dr["Add_1"].ToString(),dr["Add_2"].ToString(), dr["Add_3"].ToString(), dr["District"].ToString(), dr["Post_Code"].ToString(),dr["Post_Stub"].ToString(),dr["House_No"].ToString(),dr["House_No_Suffix"].ToString(),dr["UPRN"].ToString(),dr["Lo_Code"].ToString(),dr["Full_Address"].ToString(),iCounter));
                iCounter++;
                //addressList.Add(new Address { ID = Convert.ToInt32(dr["Lo_Code"]), UPRN = dr["UPRN"].ToString(), Add_1 = dr["Add_1"].ToString() });

            }
            return addressList;
        }


        private DataTable filterTable(string strFilter, DataTable dt)
        {


            try
            {
                DataTable postFilter = dt.Clone();
                postFilter.BeginLoadData();
                foreach (DataRow drx in dt.Select(strFilter))
                {
                    postFilter.ImportRow(drx);
                }
                return postFilter;

            }
            catch (Exception ex)
            {
    
                DataTable dt2 = new DataTable();
                return dt2;
            }


        }

    }
}
